/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jobportal.student;

import java.sql.Statement;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import jobportal.Dbconn;
import jobportal.EmailSender;

/**
 *
 * @author Vishwakarma
 */
public class applyjob extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        RequestDispatcher rd = request.getRequestDispatcher("searchresult.jsp");
        try {
            Connection con = Dbconn.getConn();
            Statement st = con.createStatement();
            ResultSet rs;



            HttpSession session = request.getSession();
            String uname = (String) session.getAttribute("username");
            String jobid = request.getParameter("jobid");
            if (jobid == null || jobid.equals("")) {
                response.sendRedirect("../index.jsp?status=Invalid jobid");
            }
            if (uname == null || uname.equals("")) {
                response.sendRedirect("../index.jsp?status=You must login to apply this job");
            }
            int jobminexp = -1, jobmaxexp = -1, jobcarierlevel = -1;
            float jobminmarks10 = 0, jobminmarks12 = 0, jobminmarksug = 0, jobminmarkspg = 0;
            String jobqualification = "", jobgender = "";



            String sql = "select student.status,adminverify,college.status,college.status from student,college where student.clgid=college.clgid and student.username='" + uname + "'";
            rs = st.executeQuery(sql);
            String msg = "";
            if (rs.next()) {

                if (rs.getString(1) == null || !rs.getString(1).toString().equalsIgnoreCase("Verified")) {
                    msg += "<br>college not verified your profile!please contact ur placement officer<br>";
                }
                if (rs.getString(2) == null || !rs.getString(2).equalsIgnoreCase("Active")) {
                    msg += "<br>Your Profile is temporarly suspended by Admin..<br>";
                }
                if (rs.getString(3) == null || !rs.getString(3).equalsIgnoreCase("Active")) {
                    msg += "<br>Your college profile is temporarly suspended by Admin..<br>" + rs.getString(3);
                }
            }





            sql = "select gender,minexp,maxexp,careerlevel,minmarks10,minmarks12,minmarksug,minmarkspg,qualification from jobs where jobid='" + jobid + "'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                jobminexp = rs.getInt("minexp");
                jobmaxexp = rs.getInt("maxexp");
                jobcarierlevel = rs.getInt("careerlevel");
                jobminmarks10 = rs.getFloat("minmarks10");
                jobminmarks12 = rs.getFloat("minmarks12");
                jobminmarksug = rs.getFloat("minmarksug");
                jobminmarkspg = rs.getFloat("minmarkspg");
                jobqualification = rs.getString("qualification");
                jobgender = rs.getString("gender");
            }
            //checking gender of student
            rs = st.executeQuery("select gender from student where username='" + session.getAttribute("username") + "'");
            String stugender = "";
            if (rs.next()) {
                if (rs.getString(1) != null) {
                    stugender = rs.getString(1);
                }
            }

            if (!jobgender.equals("") && !jobgender.equals("Both")) {
                if (!jobgender.equals(stugender)) {
                    msg = msg + "only " + jobgender + " can apply this job";
                }
            }
            //checking exprience of student
            int exp = 0;
            sql = "select exp from exprience where username='" + uname + "'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                exp = rs.getInt(1);

            }

            if (jobminexp > 0) {

                if (exp < jobminexp) {
                    msg = msg + "<br/>Minimum Exprience::" + jobminexp;
                }
            }
            if (jobmaxexp > 0) {

                if (exp > jobmaxexp) {
                    msg = msg + "<br/> Maximum Exprience::" + jobmaxexp;
                }
            }

            rs = st.executeQuery("select * from education where username='" + uname + "'");
            if (!rs.next()) {
                request.setAttribute("status", "Your Profile is incomplete! <a href=\"editresume.jsp\">Click Here</a> to Complete Your Profile");
                rd.forward(request, response);
            }
            //checking course
            String course = "";
            boolean flag = false;
            sql = "select course from education where username='" + uname + "'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                try {
                    course = rs.getString(1);
                    if (jobqualification.contains(course)) {
                        flag = true;
                    }
                } catch (Exception e) {
                }

            }
            if (flag == false) {
                msg += "<br/> Qualification must be in " + jobqualification;
            }

            //checking careerlevel
            int cl = 0;
            sql = "select max(type) from education where username='" + uname + "'";
            String sql1 = sql;
            rs = st.executeQuery(sql);
            if (rs.next()) {
                cl = rs.getInt("max(type)");
                //out.println("carlev "+cl);
            }

            if (jobcarierlevel > cl) {
                msg += "<br/> Must be ";
                switch (jobcarierlevel) {
                    case 1:
                        msg += "10";
                        break;
                    case 2:
                        msg += "12";
                        break;
                    case 3:
                        msg += " Under Graduate";
                        break;
                    case 4:
                        msg += " Post Graduate";
                        break;

                }
            } else {
                float mark10 = 0, mark12 = 0, markug = 0, markpg = 0;
                sql = "select * from education where username='" + uname + "'";
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    int type = rs.getInt("type");
                    switch (type) {
                        case 1:
                            mark10 = rs.getFloat("marks");
                            break;
                        case 2:
                            mark12 = rs.getFloat("marks");
                            break;
                        case 3:
                            markug = rs.getFloat("marks");
                            break;
                        case 4:
                            markpg = rs.getFloat("marks");
                    }
                }
                switch (jobcarierlevel) {
                    case 4:
                        if (markpg < jobminmarkspg) {
                            msg += "<br/>Minimum Marks in post graduate " + jobminmarkspg + "% your Mark::" + markpg + "%";
                        }
                    case 3:
                        if (markug < jobminmarksug) {
                            msg += "<br/>Minimum Marks in graduatation " + jobminmarksug + "% your Mark::" + markug + "%";
                        }
                    case 2:
                        if (mark12 < jobminmarks12) {
                            msg += "<br/>Minimum Marks in 12 " + jobminmarks12 + "% your Mark::" + mark12 + "%";
                        }
                    case 1:
                        if (mark10 < jobminmarks10) {
                            msg += "<br/>Minimum Marks in 10 " + jobminmarks10 + "% your Mark::" + mark10 + "%";
                        }
                }

            }
            if (msg.equals("")) {
                if (request.getParameter("Submit") != null && request.getParameter("Submit").equals("Apply This Job")) {
                    sql = "insert into job_applied(jobid,username) values('" + request.getParameter("jobid") + "','" + uname + "')";

                    if (st.executeUpdate(sql) > 0) {
                        request.setAttribute("status", "<font  color=\"green\">Job Has been successfully Applied<br /> To view all applied jobs<a href=my_sent_application.jsp>click here</a></font>");
                        st.executeUpdate("delete from savedjob where username='" + uname + "' and jobid='" + jobid + "'");

                        //Email message
                        rs = st.executeQuery("select * from emailmessage where id=3");
                        String subject="",content="";
                        if (rs.next()) {
                            subject = rs.getString("subject");
                            content = rs.getString("content");
                            
                            Statement st1=con.createStatement();
                            ResultSet rs1=st1.executeQuery("select jobtitle,compname from jobs,company where jobs.compid=company.compid and jobs.jobid='"+jobid+"'");
                            String jobtitle="",compname="";
                            if(rs1.next()){
                                jobtitle=rs1.getString("jobtitle");
                                compname=rs1.getString("compname");
                            }
                            rs1=st1.executeQuery("select * from student where username='"+uname+"'");
                            String email="",name="";
                            if(rs1.next()){
                                email=rs1.getString("email");
                                name=rs1.getString("name");
                            }
                            content=content.replace("%jobtitle%", jobtitle);
                            content=content.replace("%compname%", compname);
                            content=content.replace("%username%", name);
                            out.println("email:"+email+" name:"+name+" jobtitle: "+jobtitle+"  compname:"+compname+" ");
                            EmailSender.sendMail(email, subject, content);
                        }

                       rd.forward(request, response);
                    } else {
                        request.setAttribute("status", "<font color=\"red\">Error! Job Already Applied</font>");
                        rd.forward(request, response);
                    }
                } else if (request.getParameter("Submit") != null && request.getParameter("Submit").equals("Save Job")) {
                    rs = st.executeQuery("select * from job_applied where username='" + uname + "' and jobid='" + request.getParameter("jobid") + "'");
                    if (!rs.next()) {
                        sql = "insert into savedjob(jobid,username) values('" + request.getParameter("jobid") + "','" + uname + "')";
                        if (st.executeUpdate(sql) > 0) {
                            request.setAttribute("status", "<font  color=\"green\">Job Has been Saved Successfully</font>");
                            rd.forward(request, response);
                        } else {
                            request.setAttribute("status", "<font color=\"red\">Error! Job Already Saved</font>");
                            rd.forward(request, response);
                        }
                    } else {
                        request.setAttribute("status", "<font color=\"red\">Error!You Cannot save this job, Job Already Applied</font>");
                        rd.forward(request, response);
                    }
                }

            } else {
                request.setAttribute("status", "<font color=\"red\">Sorry! You Are not Eligible Candidate<br>" + msg + "</font>");
                rd.forward(request, response);
            }
            rd.forward(request, response);
        } catch (Exception e) {
            if (e.toString().contains("Duplicate entry") && request.getParameter("Submit").equals("Apply This Job")) {
                request.setAttribute("status", "<font color=\"red\">Job has already been applied::<br /> To view all applied jobs<a href=my_sent_application.jsp>click here</a></font>");
            } else if (e.toString().contains("Duplicate entry")) {
                request.setAttribute("status", "<font color=\"red\">Job already saved</font>");
            } else {
                request.setAttribute("status", e);
            }
            rd.forward(request, response);
        } finally {
            out.close();
        }
    }

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);


    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);


    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";

    }// </editor-fold>
}
